package sampledb.tools

import sweet.helper.jdbc._
import sweet.helper._
object JdbcDemo {  
  def main(args: Array[String]){
    val jdbc = JdbcHelper("sample_db", "sample_db123", "jdbc:mysql://localhost/sample_db", "com.mysql.jdbc.Driver") 
    
    println("withConn")
    jdbc.withConn{ conn =>
      println("getAutoCommit? " + conn.getAutoCommit)
      println("getCatalog? " + conn.getCatalog)
    }
    
    println("withStmt")
    jdbc.withStmt{ stmt =>
      val rsh = new ResultSetHelper(stmt.executeQuery("select version() as ver"))
      if(rsh.next)
        println(rsh("ver"))
      rsh.eachRowWithName{ (rowIdx, name, value) => 
        println("rowIdx "+rowIdx+" "+name+": "+value) 
      } 
      rsh.close
    }
    
    println("withPStmt")
    jdbc.withPStmt("insert into user(email, password) values(?,?)"){ pstmt =>
      for(i <- 1 to 10){
        pstmt.setString(1, "test"+(JDate.now.millis+i))
        pstmt.setString(2, "secret")
        val count = pstmt.executeUpdate()
        println("Update result: "+count) 
      }
    }
    
    println("single update: "+jdbc.update("insert into user(email, password) values('test"+(JDate.now.millis+11)+"', 'secret')"))
    println("params update: "+jdbc.update("insert into user(email, password) values(?,?)", "test"+(JDate.now.millis+12), "secret"))
    
    println("Single Query")
    jdbc.query("select * from user limit 3"){ rs =>
      rs.eachColWithName{ (name, value) => println(name+": "+value) }
    }    
    println("Single Query2")
    jdbc.query("select email from user where id=1"){ rs => 
      println("First email " + rs("email"))
    }    
    println("Params Query")
    jdbc.query("select email from user where id < ?", 3){ rs => 
      rs.eachColWithName{ (name, value) => println(name+": "+value) }
    }
    println("Query with limit")
    jdbc.queryLimit(3, "select email from user where id>?", 5){ rs => 
      rs.eachColWithName{ (name, value) => println(name+": "+value) }
    }    
    
    println("Query queryVal value")
    println("res = "+jdbc.queryVal[Int]("select count(*) from user"){rsh=> rsh.rs.getInt(1)})
    println("Query queryInt value")
    println("count = "+jdbc.queryInt("select count(*) from user"))
    println("Query queryString value")
    println("res = "+jdbc.queryString("select version() as ver"))
    println("Query queryDouble value")
    println("res = "+jdbc.queryDouble("select 1/3"))
    println("Query queryBoolean value")
    println("res = "+jdbc.queryBoolean("select active=0 from user where id=1"))
    println("Query queryBoolean value")
    println("res = "+jdbc.queryBoolean("select active=1 from user where id=1"))
    println("Query queryBoolean value")
    println("res = "+jdbc.queryDate("select now()"))
    
    println("Query Map")
    val userMap = jdbc.queryMap("select * from user limit 3")
    for((k,v) <- userMap) println(k+": "+v)
    
    println("Query ListMap")
    val userListMap = jdbc.queryList("select * from user limit 3")
    for(map <- userListMap)
      for((k,v) <- map) println(k+": "+v)
      
    println("Query Map with parameters ")
    val userMap2 = jdbc.queryMap("select * from user where id<? limit 3", 10)
    for((k,v) <- userMap2) println(k+": "+v)
    
    println("Query ListMap with parameters ")
    val userListMap2 = jdbc.queryList("select * from user where id>? limit 3", 10)
    for(map <- userListMap2)
      for((k,v) <- map) println(k+": "+v)
      
    println("Using a open conn for multiple queries and updates.")
    jdbc.withOpenJdbc{ openJdbc =>
      val count = openJdbc.queryInt("select count(*) from user where email=?", "tester1")      
      if(count==0)
        openJdbc.update("insert into user(email, password) values(?,?)", "tester1", "secret")
      else
        openJdbc.update("update user set password=? where username=?", "secret2")
    }
  }
}
